﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Utils;
using DataHelper;
//年终奖
namespace DAL
{
   public class SalaryYearDao
   { 
       
       //年终奖列表
       public DataTable GetList(string serWord, string strYear, string deptID,string setsID)
       {
           StringBuilder strFilter = new StringBuilder("Select a.*,b.DeptName,b.EmpName,b.DutyName,b.StateName,b.SalaryAccount,b.ZipCode,b.JoinTime from Wage_SalaryListYear a inner join  View_Emp b on a.EmpCode=b.EmpCode Where a.SalYear=" + strYear);
           if (deptID != "0") strFilter.Append(" and b.DeptID=" + deptID);

           if (setsID != "0") strFilter.Append(" and a.SetsID=" + setsID);

           if (!string.IsNullOrWhiteSpace(serWord))
               strFilter.Append(" and  (b.EmpName like '%" + serWord + "%' OR a.EmpCode like '%" + serWord + "%' )");

           strFilter.Append(" Order by b.DeptName ASC, a.EmpCode ASC");
           return SQLHelper.ExecuteSqlGetDrToDt(strFilter.ToString());
       }

       public DataTable GetOut(string serWord, string strYear, string deptID, string setsID)
       {
           //基本工资 职补 全勤 小计  未出勤月	累计请事假	请产假	其它       实际发放年终奖金
            //n01k  n02k  n03k  n11k    n20k     n21k        n22k   n23k       n31k
           StringBuilder strFilter = new StringBuilder("Select  b.DeptName as 部门,b.SalaryAccount as 银行账号,b.EmpCode as 工号,b.EmpName as 姓名,b.DutyName as 职位,b.StateName as 状态,a.WorkMonthInYear as 出勤月数,b.JoinTime as 入职时间,a.n01k as 基本工资,a.n02k as 职补 ,a.n03k as 全勤,a.n11k as 小计, a.n20k as 未出勤月,a.n21k as 请事假,a.n22k as 请产假,a.n23k as 其它,a.n31k as 年终奖,a.Memo as 备注,b.ZipCode as 身份证   from Wage_SalaryListYear a inner join  View_Emp b on a.EmpCode=b.EmpCode Where a.SalYear=" + strYear);
           if (deptID != "0") strFilter.Append(" and b.DeptID=" + deptID);

           if (setsID != "0") strFilter.Append(" and a.SetsID=" + setsID);

           if (!string.IsNullOrWhiteSpace(serWord))
               strFilter.Append(" and  (b.EmpName like '%" + serWord + "%' OR a.EmpCode like '%" + serWord + "%' )");

           strFilter.Append(" Order by b.DeptName ASC, a.EmpCode ASC");
           return SQLHelper.ExecuteSqlGetDrToDt(strFilter.ToString());
       }
       //根据SalID 获取一条年终奖
       public DataTable GetYearEnd(string salID)
       {
           return SQLHelper.ExecuteSqlGetDrToDt("Select * from view_SalaryListYear Where SalID=" + salID);
       }
       public bool Delete(string salYear, string empCode, string setsID)
       {
           if (!string.IsNullOrWhiteSpace(empCode))
               return SQLHelper.ExecuteSql("Delete From  Wage_SalaryListYear Where SetsID=" + setsID +" and Statu='Y' and EmpCode='" + empCode + "' and SalYear=" + salYear );
           else
               return SQLHelper.ExecuteSql("Delete From  Wage_SalaryListYear Where  SetsID=" + setsID + " and Statu='Y'  and SalYear=" + salYear);
       }
       //发放工资 就是更改状态为N
       public bool IsApply(string salYear, string setsID)
       {
           return SQLHelper.ExecuteSql("Update  Wage_SalaryListYear set  Statu='N' Where  SetsID=" + setsID + " and Statu='Y'  and SalYear=" + salYear);
       
       }
       //判断 某年是否在年终奖
       public bool IsExist(string salYear, string setsID)
       {
           DataTable dt = SQLHelper.ExecuteSqlGetDataSet("Select EmpCode from Wage_SalaryListYear Where  SetsID=" + setsID + " and  SalYear=" + salYear).Tables[0];
           if (dt.Rows.Count > 0)
               return true;
           else
               return false;
       }
       //判断 某年 某个工号有没有生成年终奖
       public bool IsExist(string salYear, string empCode, string setsID)
       {
           DataTable dt = SQLHelper.ExecuteSqlGetDataSet("Select EmpCode from Wage_SalaryListYear Where SetsID=" + setsID + " and EmpCode='" + empCode + "' and SalYear=" + salYear).Tables[0];
           if (dt.Rows.Count > 0)
               return true;
           else
               return false;
       }
       //把年终奖写入到工资表
       public bool CopyYearToSalTable(string fromYear, string toYear, string toMonth, string setsID)
       {
           //首先获取 年终奖在张涛中的字段 ,要向没有月结的工资表中写的字段
           string wageItem = new SetsItemDao().GetWageItem(setsID, "年终奖");
           string strSQL = "update t1 set " + wageItem + " =t2.n31k  from  Wage_SalaryList t1,Wage_SalaryListYear t2 where t1.Statu='Y' and t1.SalYear=" + toYear + " and t1.SalMonth=" + toMonth + " and t1.SetsID=" + setsID + " and t2.SalYear=" + fromYear + " and t1.EmpCode=t2.EmpCode ";
           return SQLHelper.ExecuteSql(strSQL);
       }

       public bool CreateYear(string setsID, string strYear)
       {
           SqlParameter para0 = new SqlParameter("@theYear", SqlDbType.SmallInt);
           SqlParameter para1 = new SqlParameter("@setsID", SqlDbType.Int);

           para0.Value = Int32.Parse(strYear);
           para1.Value = Int32.Parse(setsID);

           SqlParameter[] spList = { para0, para1 };
           return SQLHelper.ExecutePorcedure("spJimCreateSLYear", spList);
       }
    }
}
